-- @owner: wang_zhengyuan
-- @date: 2022/02/11
-- @testpoint: 本地临时表组合索引，查询使用in条件
--step1:建本地临时表;expect:建表成功
drop table if exists table_idx_in_local_007;
 
create temporary table table_idx_in_local_007(
staff_id int not null,
course_id number(28,16),
higest_degree varchar(8),
graduate_school varchar(64) not null,
graduate_date date,
t_sql_note varchar(70)) with (storage_type=ustore);
 
--step2:创建sequence;expect:成功
drop sequence if exists v_seq;
 
create sequence v_seq increment by 1 start with 1;
 
--step3:创建索引;expect:创建索引成功
drop index if exists idx_in_007_001;
 
create index idx_in_007_001 on table_idx_in_local_007(staff_id,course_id,higest_degree);
 
--step4:创建索引;expect:创建索引成功
drop index if exists idx_in_007_002;
 
create index idx_in_007_002 on table_idx_in_local_007(staff_id,graduate_school,graduate_date);
 
--step5:插入数据;expect:成功
insert into table_idx_in_local_007 values(3, v_seq.nextval,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_local_007 values(4, v_seq.nextval,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_local_007 values(5, v_seq.nextval,'master','northwestern polytechnical university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_local_007 values(6, v_seq.nextval,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_local_007 values(7, v_seq.nextval,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_local_007 values(8, v_seq.nextval,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_local_007 values(10,v_seq.nextval,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_local_007 values(11,v_seq.nextval,'master','northwestern polytechnical university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_local_007 values(12,v_seq.nextval,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
begin
    for i in 1..300 loop
    insert into table_idx_in_local_007 values(mod(i,25),v_seq.nextval/i,'master','northwest a&f university',
    add_months('2017-03-15 12:00:00',i),'211&985');
    end loop;
end;
/
 
begin
    for i in 1..200 loop
    insert into table_idx_in_local_007 values(mod(i,114),v_seq.nextval/i,'master','northwestern university',
    add_months('2017-03-16 12:00:00',mod(i,18)),'211&985');
    end loop;
end;
/
 
begin
    for i in 1..400 loop
    insert into table_idx_in_local_007 values(mod(i,17),v_seq.nextval/i,'master','peking university',
    add_months('2015-03-15 12:00:00',i),'211');
    end loop;
end;
/
 
--step6:本地临时表组合索引，查询使用in条件;expect:查询数据成功
select staff_id,course_id,graduate_school from table_idx_in_local_007
where staff_id in (9,23,78,230,54) and higest_degree = 'master' order by 1,2,3 limit 10;

--step7:环境清理;expect:成功
drop sequence if exists v_seq;
 
drop index if exists idx_in_007_001;
 
drop index if exists idx_in_007_002;
 
drop table if exists table_idx_in_local_007;
 
